{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 进阶篇：样式化—StyleFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**DataFrames导出到样式化的Excel文件从未如此容易**\n",
    "\n",
    "**StyleFrame**是一个封装panda和openpyxl的库，允许在Excel中轻松设置数据帧的样式。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 理论基础\n",
    "\n",
    "- Pandas的DataFrame非常棒。\n",
    "\n",
    "- 处理大量数据并不容易，DataFrame可以帮助我们以最佳方式进行管理。\n",
    "\n",
    "- 有很多种方式可以显示输出，其中之一就是excel文件。\n",
    "\n",
    "- Excel文件易于理解，可以离线查看，可以通过电子邮件发送，而且大部分人都熟悉它。\n",
    "\n",
    "这就是为什么很多时候我们会选择excel文件作为输出。\n",
    "\n",
    "**StyleFrame包允许我们以类似于DataFrameapi的方式设计数据上的excel文件。**\n",
    "\n",
    "**它为我们省去了使用excel工作簿的麻烦，也省去了将其与存储在DataFrame中的数据进行匹配的痛苦。**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 安装\n",
    "\n",
    "> pip install styleframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 基础\n",
    "\n",
    "**Styler**：\n",
    "> Styler类表示单元格的样式。\n",
    "\n",
    "\n",
    "\n",
    "**utils**：\n",
    "> utils模块包含常用样式元素的辅助类，如数字和日期格式、颜色和边框类型。\n",
    "\n",
    "\n",
    "\n",
    "**Container**：\n",
    "> Container类表示一个单元格，一个值/样式对。\n",
    "\n",
    "\n",
    "\n",
    "**StyleFrame**：\n",
    "> StyleFrame是您将拥有的主要交互点。它包装将要设置样式的DataFrame对象。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 引入依赖包\n",
    "import pandas as pd\n",
    "from styleframe import StyleFrame, Styler, utils   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 入门案例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Time</th>\n",
       "      <th>Expect</th>\n",
       "      <th>Actual</th>\n",
       "      <th>Pass/Fail</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.496728e+09</td>\n",
       "      <td>Hey</td>\n",
       "      <td>Hello</td>\n",
       "      <td>Failed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.496728e+09</td>\n",
       "      <td>how</td>\n",
       "      <td>how</td>\n",
       "      <td>Passed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.496728e+09</td>\n",
       "      <td>are</td>\n",
       "      <td>are</td>\n",
       "      <td>Passed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.496728e+09</td>\n",
       "      <td>you</td>\n",
       "      <td>u</td>\n",
       "      <td>Failed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.496728e+09</td>\n",
       "      <td>today?</td>\n",
       "      <td>today?</td>\n",
       "      <td>Passed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Time  Expect  Actual Pass/Fail\n",
       "0  1.496728e+09     Hey   Hello    Failed\n",
       "1  1.496728e+09     how     how    Passed\n",
       "2  1.496728e+09     are     are    Passed\n",
       "3  1.496728e+09     you       u    Failed\n",
       "4  1.496728e+09  today?  today?    Passed"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'Time': [1.496728e+09, 1.496728e+09, 1.496728e+09, 1.496728e+09, 1.496728e+09],\n",
    "    'Expect': ['Hey', 'how', 'are', 'you', 'today?'],\n",
    "    'Actual': ['Hello', 'how', 'are', 'u', 'today?'],\n",
    "    'Pass/Fail': ['Failed', 'Passed', 'Passed', 'Failed', 'Passed']\n",
    "    },\n",
    "    columns=['Time', 'Expect', 'Actual', 'Pass/Fail'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "创建一个StyleFrame对象，参数包含一个Dataframe和默认的样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create StyleFrame object that wrap our DataFrame and assign default style.\n",
    "default_style = Styler(font=utils.fonts.aharoni, font_size=14)\n",
    "sf = StyleFrame(df, styler_obj=default_style)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "设置标题样式：标题加粗，字体大小为18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0593d90>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Style the headers of the table\n",
    "header_style = Styler(bold=True, font_size=18)\n",
    "sf.apply_headers_style(styler_obj=header_style)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在测试结果为passed的单元格设置背景颜色为绿色"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0593d90>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set the background color to green where the test marked as 'passed'\n",
    "passed_style = Styler(bg_color=utils.colors.green, font_color=utils.colors.white)\n",
    "sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pass/Fail'] == 'Passed'],\n",
    "                          cols_to_style='Pass/Fail',\n",
    "                          styler_obj=passed_style,\n",
    "                          overwrite_default_style=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在测试结果为passed的单元格设置背景颜色为绿色"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0593d90>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Set the background color to red where the test marked as 'failed'\n",
    "failed_style = Styler(bg_color=utils.colors.red, font_color=utils.colors.white)\n",
    "sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pass/Fail'] == 'Failed'],\n",
    "                          cols_to_style='Pass/Fail',\n",
    "                          styler_obj=failed_style,\n",
    "                          overwrite_default_style=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "writer = sf.to_excel('data/styleframe测试数据导出.xlsx',\n",
    "                     # Add filters in row 0 to each column.为每一列添加筛选\n",
    "                     row_to_add_filters=0, \n",
    "                     # Freeze the columns before column 'A' (=None)\n",
    "                     # and rows above '2' (=1).冻结窗口\n",
    "                     columns_and_rows_to_freeze='A2')\n",
    "\n",
    "writer.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![image.png](https://zhangyafei-1258643511.cos.ap-nanjing.myqcloud.com/pandas/styleframe%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%87%BA.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 进阶案例"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, let us create a DataFrame that contains data we would like to export to an .xlsx file\n",
    "\n",
    "首先，让我们创建一个dataframe，包含我们想要导出excel的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 引入依赖包\n",
    "from datetime import date\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Col A</th>\n",
       "      <th>Col B</th>\n",
       "      <th>Col C</th>\n",
       "      <th>Percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-09-05</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>33</td>\n",
       "      <td>0.113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1947-11-29</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>-6</td>\n",
       "      <td>0.504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-01-15</td>\n",
       "      <td>-3</td>\n",
       "      <td>116</td>\n",
       "      <td>9</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Col A  Col B  Col C  Percentage\n",
       "0  1995-09-05      1     15     33       0.113\n",
       "1  1947-11-29   2004      3     -6       0.504\n",
       "2  2000-01-15     -3    116      9       0.005"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns = ['Date', 'Col A', 'Col B', 'Col C', 'Percentage']\n",
    "df = pd.DataFrame(data={'Date': [date(1995, 9, 5), date(1947, 11, 29), date(2000, 1, 15)],\n",
    "                        'Col A': [1, 2004, -3],\n",
    "                        'Col B': [15, 3, 116],\n",
    "                        'Col C': [33, -6, 9],\n",
    "                        'Percentage': [0.113, 0.504, 0.005]},\n",
    "                  columns=columns)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "only_values_df = df[columns[1:-1]]\n",
    "\n",
    "rows_max_value = only_values_df.idxmax(axis=1)\n",
    "\n",
    "df['Sum'] = only_values_df.sum(axis=1)\n",
    "df['Mean'] = only_values_df.mean(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Col A</th>\n",
       "      <th>Col B</th>\n",
       "      <th>Col C</th>\n",
       "      <th>Percentage</th>\n",
       "      <th>Sum</th>\n",
       "      <th>Mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-09-05</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>33</td>\n",
       "      <td>0.113</td>\n",
       "      <td>49</td>\n",
       "      <td>16.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1947-11-29</td>\n",
       "      <td>2004</td>\n",
       "      <td>3</td>\n",
       "      <td>-6</td>\n",
       "      <td>0.504</td>\n",
       "      <td>2001</td>\n",
       "      <td>667.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-01-15</td>\n",
       "      <td>-3</td>\n",
       "      <td>116</td>\n",
       "      <td>9</td>\n",
       "      <td>0.005</td>\n",
       "      <td>122</td>\n",
       "      <td>40.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date  Col A  Col B  Col C  Percentage   Sum        Mean\n",
       "0  1995-09-05      1     15     33       0.113    49   16.333333\n",
       "1  1947-11-29   2004      3     -6       0.504  2001  667.000000\n",
       "2  2000-01-15     -3    116      9       0.005   122   40.666667"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Our DataFrame looks like this\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nsf = StyleFrame({'Date': [date(1995, 9, 5), date(1947, 11, 29), date(2000, 1, 15)],\\n                 'Col A': [1, 2004, -3],\\n                 'Col B': [15, 3, 116],\\n                 'Col C': [33, -6, 9],\\n                 'Percentage': [0.113, 0.504, 0.005],\\n                 'Sum': [49, 2001, 122],\\n                 'Mean': [16.333333, 667.000000, 40.666667]})\\n\""
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now, once we have the DataFrame ready, lets create a StyleFrame object\n",
    "from styleframe import StyleFrame\n",
    "\n",
    "sf = StyleFrame(df)\n",
    "# it is also possible to directly initiate StyleFrame\n",
    "\"\"\"\n",
    "sf = StyleFrame({'Date': [date(1995, 9, 5), date(1947, 11, 29), date(2000, 1, 15)],\n",
    "                 'Col A': [1, 2004, -3],\n",
    "                 'Col B': [15, 3, 116],\n",
    "                 'Col C': [33, -6, 9],\n",
    "                 'Percentage': [0.113, 0.504, 0.005],\n",
    "                 'Sum': [49, 2001, 122],\n",
    "                 'Mean': [16.333333, 667.000000, 40.666667]})\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The StyleFrame object will auto-adjust the columns width and the rows height but they can be changed manually\n",
    "\n",
    "StyleFrame可以自动调整列宽和行高，但是它们也可以被手动修改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0617370>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf.set_column_width_dict(col_width_dict={\n",
    "    ('Col A', 'Col B', 'Col C'): 15.3,\n",
    "    ('Sum', 'Mean'): 30,\n",
    "    ('Percentage', ): 12\n",
    "})\n",
    "\n",
    "# excel rows starts from 1\n",
    "# row number 1 is the headers\n",
    "# len of StyleFrame (same as DataFrame) does not count the headers row\n",
    "all_rows = sf.row_indexes\n",
    "sf.set_row_height_dict(row_height_dict={\n",
    "    all_rows[0]: 45,  # headers row\n",
    "    all_rows[1:]: 25\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Applying number formats\n",
    "数字格式应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0617370>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from styleframe import Styler, utils\n",
    "\n",
    "\n",
    "sf.apply_column_style(cols_to_style='Date',\n",
    "                      styler_obj=Styler(date_format=utils.number_formats.date,\n",
    "                                        font=utils.fonts.calibri,\n",
    "                                        bold=True))\n",
    "\n",
    "sf.apply_column_style(cols_to_style='Percentage',\n",
    "                      styler_obj=Styler(number_format=utils.number_formats.percent))\n",
    "\n",
    "sf.apply_column_style(cols_to_style=['Col A', 'Col B', 'Col C'],\n",
    "                      styler_obj=Styler(number_format=utils.number_formats.thousands_comma_sep))                     \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, let's change the background color of the maximum values to red and the font to white\n",
    "\n",
    "接下来，我们将每列的最大值的背景颜色改为红色，字体颜色改为白色\n",
    "\n",
    "we will also protect those cells and prevent the ability to change their value\n",
    "\n",
    "我们也将设置表格为保护模式，防止他们的值被修改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "style = Styler(bg_color=utils.colors.red,\n",
    "               bold=True,\n",
    "               font_color=utils.colors.white,\n",
    "               protection=True,\n",
    "               underline=utils.underline.double,\n",
    "               number_format=utils.number_formats.thousands_comma_sep)\n",
    "        \n",
    "for row_index, col_name in rows_max_value.iteritems():\n",
    "    sf[col_name][row_index].style = style"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And change the font and the font size of Sum and Mean columns\n",
    "\n",
    "修改Sum和Mean列的字体和字体大小"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0617370>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf.apply_column_style(cols_to_style=['Sum', 'Mean'],\n",
    "                      style_header=True,\n",
    "                      styler_obj=Styler(font_color='#40B5BF',\n",
    "                                        font_size=18,\n",
    "                                        bold=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change the background of all rows where the date is after 14/1/2000 to green\n",
    "\n",
    "修改日期大于14/1/2000的所有行背景颜色为绿色"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<styleframe.style_frame.StyleFrame at 0x182a0617370>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sf.apply_style_by_indexes(indexes_to_style=sf[sf['Date'] > date(2000, 1, 14)],\n",
    "                          cols_to_style='Date',\n",
    "                          styler_obj=Styler(bg_color=utils.colors.green,\n",
    "                                            date_format=utils.number_formats.date,\n",
    "                                            bold=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's export to Excel but not before we use more of StyleFrame's features:\n",
    "\n",
    "- Change the page writing side\n",
    "- Freeze rows and columns\n",
    "- Add filters to headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.io.excel._openpyxl._OpenpyxlWriter at 0x182a06314c0>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ew = StyleFrame.ExcelWriter('data/sf教学.xlsx')\n",
    "sf.to_excel(excel_writer=ew,\n",
    "            sheet_name='1',\n",
    "            right_to_left=False,\n",
    "            columns_and_rows_to_freeze='B2', # will freeze the rows above 2 (=row 1 only) and columns that before column 'B' (=col A only)\n",
    "            row_to_add_filters=0,\n",
    "            allow_protection=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "ew.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adding another excel sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.io.excel._openpyxl._OpenpyxlWriter at 0x182a06314c0>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "other_sheet_sf = StyleFrame({'Dates': [date(2016, 10, 20), date(2016, 10, 21), date(2016, 10, 22)]},\n",
    "                            styler_obj=Styler(date_format=utils.number_formats.date))\n",
    "                            \n",
    "other_sheet_sf.to_excel(excel_writer=ew, sheet_name='2')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Don't forget to save"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "ew.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "生成的文件 sheet1\n",
    "![sheet1](https://zhangyafei-1258643511.cos.ap-nanjing.myqcloud.com/pandas/Snipaste_2024-01-01_12-30-34.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "sheet2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![sheet2](https://zhangyafei-1258643511.cos.ap-nanjing.myqcloud.com/pandas/Snipaste_2024-01-01_12-32-05.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## [API Documentation](https://styleframe.readthedocs.io/en/latest/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### styleframe\n",
    "- StyleFrame\n",
    "    - StyleFrame.ExcelWriter()\n",
    "    - StyleFrame.add_color_scale_conditional_formatting()\n",
    "    - StyleFrame.apply_column_style()\n",
    "    - StyleFrame.apply_headers_style()\n",
    "    - StyleFrame.apply_style_by_indexes()\n",
    "    - StyleFrame.read_excel()\n",
    "    - StyleFrame.read_excel_as_template()\n",
    "    - StyleFrame.rename()\n",
    "    - StyleFrame.set_column_width()\n",
    "    - StyleFrame.set_column_width_dict()\n",
    "    - StyleFrame.set_row_height()\n",
    "    - StyleFrame.set_row_height_dict()\n",
    "    - StyleFrame.style_alternate_rows()\n",
    "    - StyleFrame.to_excel()\n",
    "\n",
    "\n",
    "### styler\n",
    "-  Styler\n",
    "    - Styler.combine()\n",
    "\n",
    "\n",
    "### utils\n",
    "-  number_formats\n",
    "    - number_formats.decimal_with_num_of_digits()\n",
    "\n",
    "-  colors\n",
    "-  fonts\n",
    "-  borders\n",
    "-  border_locations\n",
    "-  horizontal_alignments\n",
    "-  vertical_alignments\n",
    "-  underline\n",
    "-  fill_pattern_types\n",
    "-  conditional_formatting_types"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "165px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
